<?php

/**
 * @version		$Id: hello.php 15 2009-11-02 18:37:15Z chdemko $
 * @package		Joomla16.Tutorials
 * @subpackage	Components
 * @copyright	Copyright (C) 2005 - 2010 Open Source Matters, Inc. All rights reserved.
 * @author		Christophe Demko
 * @link		http://joomlacode.org/gf/project/helloworld_1_6/
 * @license		License GNU General Public License version 2 or later
 */

// No direct access to this file
defined('_JEXEC') or die('Restricted access');

// import Joomla modelitem library
jimport('joomla.application.component.modelitem');

/**
 * HelloWorld Model
 */
class bgModelbg extends JModel
{	    
    function getAllUsertype(){
	   $qr = "SELECT id,title FROM kc2vr_usergroups LIMIT 7,5";
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;
	}
    
    function getAllMailGroup(){
	   $qr = "SELECT MAGR_CODE,MAGR_NAME FROM magr";
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;
	}
    
    function getAllArea(){
	   $qr = "SELECT AREA_CODE,AREA_NAME FROM area ORDER BY AREA_CODE ASC";
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;
	}
    
    function getAllPrefOfArea($area_code){
       
       if($area_code == 0){
            $qr = "SELECT PREF_CODE,PREF_NAME FROM pref";
        }else{
            $qr = "SELECT PREF_CODE,PREF_NAME FROM pref WHERE PREF_AREA_CODE=".$area_code;     
        }
        
        $this->_db->setQuery($qr);
        $result = $this->_db->loadObjectList();
       return $result;
	}
    
    function getAllUserMail($filter){
        $sql_="";
        $w = "";
        if($filter['area_search'] != 0){ // neu thanh pho k duoc chon, khu vuc duoc chon thi select tat ca trung tam thuoc khu vuc do
            $w.=" AND PREF_AREA_CODE = ".$filter['area_search'];
        }
        
        if($filter['pref_search'] != 0){  // neu thanh pho duoc chon thi chi select tat ca trung tam thuoc thanh pho do
            $w.=" AND PREF_CODE = ".$filter['pref_search'];
        }
        
        if($filter['ocba_name'] != ''){ // search gan dung voi ten trung tam
            $w.=" AND (OCBA_NAME LIKE '%".$filter['ocba_name']."%' OR OCBA_KANA LIKE '%".$filter['ocba_name']."%')";
        }   
        $sub_select = "(SELECT DISTINCT OCBA_CODE, OCBA_NAME, OCBA_KANA, OCBA_TOWN_CODE, TOWN_PREF_CODE, PREF_CODE, PREF_AREA_CODE
                        FROM ocba
                        JOIN town ON OCBA_TOWN_CODE = TOWN_CODE
                        JOIN pref ON TOWN_PREF_CODE = PREF_CODE
                        WHERE OCBA_FLAG = 1 $w) AS ocba";
        $sql_user = "SELECT id, name, username, email, OCBA_CODE, OCBA_NAME, OCBA_KANA, PREF_CODE, PREF_AREA_CODE, group_id, sendEmail as mail_flag FROM kc2vr_users 
                    JOIN ".$sub_select." ON USEM_OCBA_CODE = OCBA_CODE
                    JOIN kc2vr_user_usergroup_map on id = user_id  ";
                    
        $sql_coch = "SELECT COCH_CODE as id, COCH_NAME as name, COCH_CODE as username, 
                            COCH_MAIL as email, OCBA_CODE, OCBA_NAME,PREF_CODE, PREF_AREA_CODE,
                            (SELECT 0) AS group_id, COCH_MAIF as mail_flag, 
                            COCH_MAIK as m_email, COCH_MAKF as m_mail_flag, 
                            COCH_MAIW as w_email, COCH_MAFW as w_mail_flag
                    FROM coch 
                    JOIN ".$sub_select." ON COCH_OCBA_CODE = OCBA_CODE ";
                    
        switch($filter['usertype_search']){
            case 'all':
                $sql_ = $sql_user." UNION ".$sql_coch;
                break;
            case 'office':
                $sql_ = $sql_user." AND group_id IN (8,9,10) ";
                break;
            case 'cooh':
                $sql_ = $sql_coch;
                break;
            case 'center':
                $sql_ = $sql_user." AND group_id IN (11,12) AND LEFT(OCBA_CODE,1)='0'";
                break;
            case 'club':
                $sql_ = $sql_user." AND group_id IN (11,12) AND LEFT(OCBA_CODE,1)='1' ";
                break;
        }

        if($filter['usertype_search'] == 'cooh'){
             $sql = "SELECT * FROM ($sql_) as result WHERE 1 = 1 " ;      
        } else {
             $sql = "SELECT * FROM ($sql_) as result WHERE mail_flag = 1 " ;      
        }
            
        
        
        
        // echo "<p>$sql</p>";
        // die;
        $this->_db->setQuery($sql);
        $result = $this->_db->loadObjectList();
        return $result;
    }

    function getAllUserMailMessage($filter){
        $sql_="";
        $w = "";
        if($filter['area_search'] != 0){ // neu thanh pho k duoc chon, khu vuc duoc chon thi select tat ca trung tam thuoc khu vuc do
            $w.=" AND PREF_AREA_CODE = ".$filter['area_search'];
        }
        
        if($filter['pref_search'] != 0){  // neu thanh pho duoc chon thi chi select tat ca trung tam thuoc thanh pho do
            $w.=" AND PREF_CODE = ".$filter['pref_search'];
        }
        
        if($filter['ocba_name'] != ''){ // search gan dung voi ten trung tam
            $w.=" AND (OCBA_NAME LIKE '%".$filter['ocba_name']."%' OR OCBA_KANA LIKE '%".$filter['ocba_name']."%')";
        }   
        $sub_select = "(SELECT DISTINCT OCBA_CODE, OCBA_NAME, OCBA_KANA, OCBA_TOWN_CODE, TOWN_PREF_CODE, PREF_CODE, PREF_AREA_CODE
                        FROM ocba
                        JOIN town ON OCBA_TOWN_CODE = TOWN_CODE
                        JOIN pref ON TOWN_PREF_CODE = PREF_CODE
                        WHERE OCBA_FLAG = 1 $w) AS ocba";
        $sql_user = "SELECT id, name, username, email, OCBA_CODE, OCBA_NAME, OCBA_KANA, PREF_CODE, PREF_AREA_CODE, group_id, sendEmail as mail_flag FROM kc2vr_users 
                    JOIN ".$sub_select." ON USEM_OCBA_CODE = OCBA_CODE
                    JOIN kc2vr_user_usergroup_map on id = user_id  ";
                    
        $sql_coch = "SELECT COCH_CODE as id, COCH_NAME as name, COCH_CODE as username, 
                            COCH_MAIL as email, OCBA_CODE, OCBA_NAME,PREF_CODE, PREF_AREA_CODE,
                            (SELECT 0) AS group_id, COCH_MAIF as mail_flag, 
                            COCH_MAIK as m_email, COCH_MAKF as m_mail_flag, 
                            COCH_MAIW as w_email, COCH_MAFW as w_mail_flag
                    FROM coch 
                    JOIN ".$sub_select." ON COCH_OCBA_CODE = OCBA_CODE ";
        
        $aut = new bgAuthorize(); //Hung: Check co quyen vao message moi hien thi user ra 
        
        switch($filter['usertype_search']){
            case 'all':
                $sql_ = $sql_user." UNION ".$sql_coch;
                break;
            case 'office':
                //Hung: Check co quyen vao message moi hien thi user ra
                $arr = $aut->getPerm_frmessage_Office('com_frmessage');
                $arr_temp = array();
                foreach ($arr as $key => $value) {
                    if($value->PRRM_INSERT || $value->PRRM_UPDATE || $value->PRRM_DELETE || $value->PRRM_EDITOWN){
                        $arr_temp[] = $value->PRRM_GRUM_CODE;
                    }
                }
                $string = implode(",", $arr_temp);
                $sql_ = $sql_user." AND group_id IN (".$string.") ";
                //$sql_ = $sql_user." AND group_id IN (8,9,10) ";
                break;
            case 'cooh':
                $sql_ = $sql_coch;
                break;
            case 'center':
                //Hung: Check co quyen vao message moi hien thi user ra
                $arr = $aut->getPerm_frmessage_CenterOrClub('com_frmessage');
                $arr_temp = array();
                foreach ($arr as $key => $value) {
                    if($value->PRRM_INSERT || $value->PRRM_UPDATE || $value->PRRM_DELETE || $value->PRRM_EDITOWN){
                        $arr_temp[] = $value->PRRM_GRUM_CODE;
                    }
                }
                $string = implode(",", $arr_temp);
                $sql_ = $sql_user." AND group_id IN (".$string.") AND LEFT(OCBA_CODE,1)='0'";
                //$sql_ = $sql_user." AND group_id IN (11,12) AND LEFT(OCBA_CODE,1)='0'";
                break;
            case 'club':
                //Hung: Check co quyen vao message moi hien thi user ra
                $arr = $aut->getPerm_frmessage_CenterOrClub('com_frmessage');
                $arr_temp = array();
                foreach ($arr as $key => $value) {
                    if($value->PRRM_INSERT || $value->PRRM_UPDATE || $value->PRRM_DELETE || $value->PRRM_EDITOWN){
                        $arr_temp[] = $value->PRRM_GRUM_CODE;
                    }
                }
                $string = implode(",", $arr_temp);
                $sql_ = $sql_user." AND group_id IN (".$string.") AND LEFT(OCBA_CODE,1)='1' ";
                //$sql_ = $sql_user." AND group_id IN (11,12) AND LEFT(OCBA_CODE,1)='1' ";
                break;
        }

        if($filter['usertype_search'] == 'cooh'){
             $sql = "SELECT * FROM ($sql_) as result WHERE 1 = 1 " ;      
        } else {
             $sql = "SELECT * FROM ($sql_) as result WHERE 1 = 1 " ;      
        }
            
        
        
        
        // echo "<p>$sql</p>";
        // die;
        $this->_db->setQuery($sql);
        $result = $this->_db->loadObjectList();
        return $result;
    }
    
    function getAllGroup(){
        $sql = "SELECT * FROM magr ORDER BY MAGR_CODE DESC";
        $this->_db->setQuery($sql);
        return $this->_db->loadObjectList();
    }

    function getAllGroupMessage(){
        $sql = "SELECT * FROM magr WHERE MAGR_USERTYPE <> 1 AND MAGR_USERTYPE <> 2 ORDER BY MAGR_CODE DESC";
        $this->_db->setQuery($sql);
        return $this->_db->loadObjectList();
    }
        
    function getAllMailDUME($search){
        $qr = "SELECT DISTINCT DUME_CODE, DUME_FULLNAME, DUME_MAIL
                FROM (                
                SELECT * 
                FROM dume
                WHERE DUME_FLAG =1
                ) AS dm
                LEFT JOIN magr ON magr.MAGR_CODE = dm.DUME_MAGR_CODE WHERE 1 ";
        
        if($search != null){
            $qr = $qr." AND MAGR_CODE = ".$search;
        }
        
        $this->_db->setQuery($qr);
        $result = $this->_db->loadObjectList();
        return $result;
    }
    function checkUserType($key){
        $qr = "SELECT MAGR_USERTYPE FROM magr WHERE MAGR_CODE=".$key;
                
        $this->_db->setQuery($qr);
        $result = $this->_db->loadObject();
        return $result->MAGR_USERTYPE;
    }
    
    //CODE CHO MAN HINH C00511
    function getAllUserMail_CENTER($filter){
        $qr = "SELECT OCBA_CODE,OCBA_NAME,TOWN_CODE,TOWN_NAME,PREF_CODE,PREF_NAME,AREA_CODE,AREA_NAME,u_id,name,USEM_GRUM_CODE FROM
                (SELECT OCBA_CODE,OCBA_NAME,TOWN_CODE,TOWN_NAME,PREF_CODE,PREF_NAME,AREA_CODE,AREA_NAME 
                                FROM ocba
                                LEFT JOIN (
                                
                                SELECT TOWN_CODE,TOWN_NAME,PREF_CODE,PREF_NAME,AREA_CODE,AREA_NAME 
                                FROM town
                                LEFT JOIN (
                                
                                SELECT PREF_CODE, PREF_NAME, AREA_CODE, AREA_NAME
                                FROM area
                                LEFT JOIN pref ON AREA_CODE = PREF_AREA_CODE
                                ) AS area_pref ON town.TOWN_PREF_CODE = area_pref.PREF_CODE
                                ) AS town_ap ON ocba.OCBA_TOWN_CODE = town_ap.TOWN_CODE) AS ocba_town 
                left join (SELECT id as u_id,name,USEM_OCBA_CODE,USEM_GRUM_CODE
                FROM kc2vr_users
                LEFT JOIN (
                
                SELECT id as up_id,title
                FROM kc2vr_usergroups
                LIMIT 7 , 5
                ) AS up ON up.up_id = kc2vr_users.USEM_GRUM_CODE) as u_up on ocba_town.OCBA_CODE = u_up.USEM_OCBA_CODE WHERE 1 ";
        if($filter['usertype_search'] != null){
            $qr = $qr." AND USEM_GRUM_CODE = ".$filter['usertype_search'];
        }
        if($filter['area_search'] != null){
            $qr = $qr." AND AREA_CODE = ".$filter['area_search'];
        }
        if($filter['pref_search'] != null){
            $qr = $qr." AND PREF_CODE = ".$filter['pref_search'];
        }
        if($filter['ocba_name'] != null){
            $qr = $qr." AND OCBA_NAME LIKE '%".$filter['ocba_name']."%' ";
        }
        //$this->_db->setQuery($qr,$filter['limitstart'], $filter['limit']);
        $result = $this->_db->loadObjectList();
        return $result;
    }
    function getTotal_CENTER(){
        $this->_db->setQuery("SELECT OCBA_CODE,OCBA_NAME,TOWN_CODE,TOWN_NAME,PREF_CODE,PREF_NAME,AREA_CODE,AREA_NAME,u_id,name,USEM_GRUM_CODE FROM
                (SELECT OCBA_CODE,OCBA_NAME,TOWN_CODE,TOWN_NAME,PREF_CODE,PREF_NAME,AREA_CODE,AREA_NAME 
                                FROM ocba
                                LEFT JOIN (
                                
                                SELECT TOWN_CODE,TOWN_NAME,PREF_CODE,PREF_NAME,AREA_CODE,AREA_NAME 
                                FROM town
                                LEFT JOIN (
                                
                                SELECT PREF_CODE, PREF_NAME, AREA_CODE, AREA_NAME
                                FROM area
                                LEFT JOIN pref ON AREA_CODE = PREF_AREA_CODE
                                ) AS area_pref ON town.TOWN_PREF_CODE = area_pref.PREF_CODE
                                ) AS town_ap ON ocba.OCBA_TOWN_CODE = town_ap.TOWN_CODE) AS ocba_town 
                left join (SELECT id as u_id,name,USEM_OCBA_CODE,USEM_GRUM_CODE
                FROM kc2vr_users
                LEFT JOIN (
                
                SELECT id as up_id,title
                FROM kc2vr_usergroups
                LIMIT 7 , 5
                ) AS up ON up.up_id = kc2vr_users.USEM_GRUM_CODE) as u_up on ocba_town.OCBA_CODE = u_up.USEM_OCBA_CODE");
        return $this->_db->loadResult();
    }
}
